package com.qsl;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneOffset;

/**
 * @Description:
 * @Author: 博客园@青石路
 * @CreateDate: 2023/4/12 14:16
 **/
public class TomcatJdbcPoolTest {

    final static String driverUrl = "jdbc:sqlserver://%s:%s; DatabaseName=%s;ApplicationIntent=ReadOnly;MultiSubnetFailover=True;";
    final static String sql = "DECLARE @Seperator VARCHAR(100) = char(27), @line_sep_replace VARCHAR(10)='' EXEC HSJY_ZQSJ_MF_SharesChange @Seperator, @line_sep_replace";

    /**
     * nowTime = 2023-04-13T09:46:59.033
     * nowTimestamp = 1681350419033
     * 2023-04-13 09:46:59.033
     * =========================
     * now = 2023-04-13
     * nowTimestamp = 1681315200000
     * 2023-04-13 00:00:00.0
     * @param args
     */
    public static void main(String[] args) {
        // queryData();
        //testLocalDateTime();
        System.out.println("=========================");
        // testLocalDate();
    }

    public static void testLocalDateTime() {
        LocalDateTime nowTime = LocalDateTime.now();
        System.out.println("nowTime = " + nowTime);
        long nowMilli = nowTime.toInstant(ZoneOffset.ofHours(8)).toEpochMilli();
        Timestamp timestamp = new Timestamp(nowMilli);
        System.out.println("nowTimestamp = " + nowMilli);
        System.out.println(timestamp);
    }

    public static void testLocalDate() {
        LocalDate now = LocalDate.now();
        System.out.println("now = " + now);
        long nowMilli = now.atStartOfDay(ZoneOffset.ofHours(8)).toInstant().toEpochMilli();
        Timestamp timestamp = new Timestamp(nowMilli);
        System.out.println("nowTimestamp = " + nowMilli);
        System.out.println(timestamp);
    }

    public static void queryData() {
        JdbcTemplate jdbcTemplate = getConnection();
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);
        SqlRowSetMetaData rsmd = sqlRowSet.getMetaData();
        int columnNum = rsmd.getColumnCount();
        while(sqlRowSet.next()) {
            for (int i = 1; i <= columnNum; i++) {
                Object obj = sqlRowSet.getObject(i);
                String sb = obj==null ? "null" : obj instanceof Number ? new BigDecimal(obj.toString()).toPlainString() : obj.toString();
                System.out.println("字段名：" + rsmd.getColumnName(i) + ", 字段类型：" + rsmd.getColumnType(i) + ", 字段值：" + sb);
            }
        }
    }

    public static JdbcTemplate getConnection() {
        PoolProperties poolproper = new PoolProperties();
        poolproper.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = String.format(driverUrl, "10.106.22.83", "1433", "APPDB");
        poolproper.setUrl(url);
        poolproper.setUsername("testenv");
        poolproper.setPassword("test@123");
        poolproper.setMaxActive(400);
        poolproper.setInitialSize(1);
        poolproper.setMaxIdle(10);
        poolproper.setMinIdle(1);
        poolproper.setRemoveAbandoned(true);
        poolproper.setRemoveAbandonedTimeout(180);
        poolproper.setDefaultAutoCommit(true);
        poolproper.setDefaultTransactionIsolation(1);

        poolproper.setTestOnBorrow(true);
        poolproper.setTestOnReturn(false);
        poolproper.setTestWhileIdle(true);
        poolproper.setValidationInterval(30000);
        poolproper.setValidationQuery("SELECT 1");
        DataSource datasource = new DataSource(poolproper);
        return new JdbcTemplate(datasource);
    }
}
